#_*_coding:utf-8_*_

import xlrd
import xlwt
import time

'''
将xls数据读取到list中，在对list处理
'''
start_time = time.localtime()
wb = xlrd.open_workbook(filename='e:/1.xls')
ws = wb.sheet_by_name('Sheet2')
dataset = []
for r in range(ws.nrows):
    col = []
    for c in range(ws.ncols):
        col.append(ws.cell(r,c).value)
    dataset.append(col)
print(len(dataset))
print(dataset)
# line = dataset[1]
# dataset.remove(line)
# print(dataset)


'''
处理list
'''
cs = ["NCMC","RIM",'SHARP公司',"中卫莱康","中辰","中锘基雅","丰尚","众鑫百合","优易","优派环宇","佰利","佳斯特","信云","元一时代","全盈通讯","凤腾伟业","创维","华唐","华录","华硕","华立","华立时代","华翼兴达","南极星","博瑞","卡尔电气","卡美欧","吉邦","启东优思","和信","唐为","垦鑫达","基思瑞","夏新","夏朗","大唐","大成","大显","大显泛泰","大诚联合","天津通广","好利通","威盛","威铂通讯","展翼","库麦","康佳","彤霖","微网信通","惠州德赛电子","扬州万事通","振华","振华欧比","掌中宝","斐讯通信","易丰展业","暂无","未知厂商","杭州东信百丰","桑菲","比酷","洪洋伟业","绵阳国虹","美翼景业","美菱","翼鸣时代","芯洲源","英华达","语信时代","赛鸿通讯","锐族","青橙","首信","高通无线","鹏智","齐乐","中维恒泰","丰尚","乐游互联","京瓷","京瓷振华","深圳峰达通"," ","null",'']
meid = ['00000000000000','0','null',' ','']

for line in dataset:
    # print(line[2])
    #删除指定的厂商和meid
    if line[1] in cs or line[2] in meid or line[1]=='' or line[2]==''or line[3]=='':
        # print(line)
        dataset.remove(line)
dataset.sort(key=lambda x:x[2])   #列表对第3个关键字排序
print(len(dataset))
print(dataset)
'''
将处理好的list写入到xls
'''
i = 0
y = 0
workbook = xlwt.Workbook(encoding='ascii')  # 设置表格字符集
worksheet = workbook.add_sheet('data')  # 设置工作簿名称
for sj in dataset:
    for a in dataset[0]:
        # print (i, y,dataset[i][y])
        worksheet.write(i, y, dataset[i][y])  # 将相应内容写入到工作薄
        y = y + 1
    i = i + 1
    y = 0  # 每次让Y的值为0   即可实现y不断的从0到3
    workbook.save('d:/2.xls')   #将表格内容保存在本地
print('处理成功的表格在d盘')
end_time = time.localtime()
print('一共用时',end_time-start_time)